package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Family;
import interfaces.IFamily;

public class CFamily implements IFamily {
  private CConnection db;

  public CFamily(CConnection db) {
	this.db = db;
  }
	
  @Override
  public boolean create(Family family) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("insert into family (name, description, observations) values ('"+family.getName()+"', '"+family.getDescription()+"',  '"+family.getObservations()+"')");
    return i > 0;
  }

  @Override
  public boolean update(Family family) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("UPDATE family SET observations='"+family.getObservations()+"', description='"+family.getDescription()+"' WHERE name='"+family.getName()+"'");
    return i > 0;
  }
  
  @Override
  public boolean delete(Family family) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    int i = s.executeUpdate("delete from family where name='"+family.getName()+"'");
    return i > 0;
  }

  @Override
  public ArrayList<Family> retrieveByName(String name) throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from family where name = '" + name + "'");

    ArrayList<Family> families = new ArrayList<Family>();
    
    while(res.next()) {
      Family f = new Family();
      f.setName(res.getString("name"));
      f.setDescription(res.getString("Description"));
      f.setObservations(res.getString("Observations"));
      families.add(f);
    }

    return families;
  }

  @Override
  public ArrayList<Family> retrieveAll() throws SQLException, ClassNotFoundException {
    Statement s = db.getStatement();
    ResultSet res = s.executeQuery("select * from family");
		
    ArrayList<Family> families = new ArrayList<Family>();
    
    while(res.next()) {
      Family f = new Family();
      f.setName(res.getString("name"));
      f.setDescription(res.getString("Description"));
      f.setObservations(res.getString("Observations"));
      families.add(f);
    }
    
    return families;
  }
}